package com.lm.scau.dyb.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabaseLockedException;
import android.database.sqlite.SQLiteOpenHelper;

import com.lm.scau.dyb.R;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.channels.FileChannel;
import java.util.List;
import java.util.Vector;

/**
 * Function:
 * auth：liming on 2016/8/4 16:12
 * mail：liming@wxchina.com
 */
public class DybDB extends SQLiteOpenHelper implements DbImpl {

    private static String DB_NAME = "Dyb_DB.db"; // 数据库名称
    private static int DB_VERSION = 3; // 版本号

    private static String DIR = "/data/data/com.lm.scau.dyb/databases/";
    public static String DB_FILEPATH = DIR + DB_NAME;

    private static SQLiteDatabase db = null;
    private static DybDB dybDB = null;
    private static Context context;

    private DybDB(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
        db = getWritableDatabase(); // 读写方式打开数据库
    }

    public static synchronized DybDB get(Context context) {
        if (null == dybDB || !db.isOpen()) {
            try {
                dybDB = new DybDB(context);
                context = context;
            } catch (SQLiteDatabaseLockedException ex) {
                ex.printStackTrace();
            }
            db.setLockingEnabled(false);// 有checkLock()，关闭数据库的lock
        }
        return dybDB;
    }

    private void checkLock() {
        while (db.isDbLockedByOtherThreads()) {
            try {
                Thread.sleep(30);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }
        checkDb();
    }

    private void checkDb() {
        if (null == db || !db.isOpen()) {
            db = getWritableDatabase(); // 读写方式打开数据库
        }
    }

    /**
     * 添加操作
     *
     * @param insertSql 对应插入字段 如：insert into person(name,age) values(?,?)
     * @param obj       对应值 如： new Object[]{person.getName(),person.getAge()};
     * @return
     */
    public boolean save(String insertSql, Object obj[]) {
        try {
            checkLock();
            db.execSQL(insertSql, obj);
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    /**
     * 添加操作
     *
     * @param tableName 表名
     * @param values    集合对象
     * @return
     */
    public boolean save(String tableName, ContentValues values) {
        try {
            checkLock();
            db.insert(tableName, null, values);
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean insertOrIgnore(String tableName, ContentValues values) {
        try {
            checkLock();
            db.insertWithOnConflict(tableName, null, values, SQLiteDatabase.CONFLICT_IGNORE);
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    @Override
    public boolean saveAll(String tableName, List<ContentValues> values) {
        try {
            db.beginTransaction();
            for (ContentValues value : values) {
                db.insert(tableName, null, value);
            }
            db.setTransactionSuccessful();
            db.endTransaction();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        } finally {
            // closeConnection(db);
        }
        return true;
    }

    /**
     * 更新操作
     *
     * @param updateSql 对应跟新字段 如： update person set name=?,age=? where personid=?"
     * @param obj       对应值 如： new Object[]{person.getName(),person.getAge(),person.getPersonid ()};
     */
    public boolean update(String updateSql, Object obj[]) {
        try {
            checkLock();
            db.execSQL(updateSql, obj);
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    /**
     * 更新操作
     *
     * @param table
     * @param values
     * @param whereClause
     * @param whereArgs
     * @return
     */
    public boolean update(String table, ContentValues values, String whereClause, String[] whereArgs) {
        try {
            checkLock();
            db.update(table, values, whereClause, whereArgs);
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    /**
     * 使用事务批量执行sql
     *
     * @param sqlList
     * @return
     */
    public boolean execSqlWithTransaction(List<String> sqlList) {
        try {
            db.beginTransaction();
            for (String sql : sqlList) {
                db.execSQL(sql);
            }
            db.setTransactionSuccessful();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        } finally {
            db.endTransaction();
        }
        return true;
    }

    /**
     * 使用事务添加数据
     *
     * @param tableName
     * @param values
     * @return
     */
    @Override
    public boolean saveWithTransaction(String tableName, List<ContentValues> values) {
        try {
//            checkLock();
            db.beginTransaction();
            for (ContentValues value : values) {
                db.insert(tableName, null, value);
            }
            db.setTransactionSuccessful();

        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        } finally {
            db.endTransaction();
        }
        return true;
    }

    /**
     * 使用事务更新数据
     *
     * @param table
     * @param values
     * @param whereClause
     * @param whereArgs
     * @return
     */
    public boolean updateWithTransaction(String table, List<ContentValues> values, String whereClause, List<String[]> whereArgs) {
        try {
            db.beginTransaction();
//            Log.v("EtionDB","updateWithTranscation cv list size: "+values.size());
            for (int counter = 0; counter < values.size(); counter++) {
                db.update(table, values.get(counter), whereClause, whereArgs.get(counter));
            }
            db.setTransactionSuccessful();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        } finally {
            db.endTransaction();
        }
        return true;
    }


    /**
     * 删除
     *
     * @param deleteSql 对应跟新字段 如： "where personid=?"
     * @param obj       [] 对应值 如： new Object[]{person.getPersonid()};
     * @return
     */
    public boolean delete(String table, String deleteSql, String obj[]) {
        try {
            checkLock();
            db.delete(table, deleteSql, obj);
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    /**
     * 查询操作
     *
     * @param findSql 对应查询字段 如： select * from person limit ?,?
     * @param obj     对应值 如： new String[]{String.valueOf(fristResult),String.valueOf( maxResult)}
     * @return
     */
    public Cursor find(String findSql, String obj[]) {
        try {
            checkLock();
            Cursor cursor = db.rawQuery(findSql, obj);
            return cursor;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        }
    }

    public boolean execSQL(String sql) {
        try {
            checkLock();
            db.execSQL(sql);
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    /**
     * 创建表
     *
     * @param createTableSql
     * @return
     */
    public boolean creatTable(String createTableSql) {
        try {
            checkLock();
            db.execSQL(createTableSql);
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean creatTable(String createTableSql, String tablename) {
        try {
            checkLock();
            db.execSQL(createTableSql);
            saveTableName(tablename);
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean creatTable2(String createTableSql, String tablename) {
        try {
            checkLock();
            db.execSQL(createTableSql);
            boolean istable = false;
            try {
                String str = "select count(*) xcount  from  EtionTable";
                db.rawQuery(str, null).close();
                istable = true;
            } catch (Exception ex) {
                istable = false;
            }
            if (!istable) {
                db = getConnection();
                StringBuffer sql = new StringBuffer();
                sql.append("CREATE TABLE IF NOT EXISTS   EtionTable (");
                sql.append(" id_DB  integer   primary key  AUTOINCREMENT , ");
                sql.append(" tablename  VARCHAR  ) ");
                db.execSQL(sql.toString());
            }
            ContentValues values = new ContentValues();
            values.put("tablename", tablename);
            db.insert("EtionTable", null, values);
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    /**
     * 表计数
     */
    public int getTableCount(String tableName) {
        Cursor cursor = null;
        try {
            cursor = db.rawQuery("select count(0) from " + tableName, null);
            return cursor.getCount();
        } catch (Exception ex) {
            ex.printStackTrace();
            return -1;
        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }
    }


    /**
     * 删除表
     */
    public boolean deleteTable(String tableName) {
        try {
            checkLock();
            db.execSQL("DROP TABLE IF EXISTS  " + tableName);
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    /**
     * 删除表
     */
    public boolean deleteTable2(String tableName) {
        try {
            checkLock();
            db.execSQL("DROP TABLE IF EXISTS  " + tableName);
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    /**
     * 判断表是否存在
     *
     * @param tablename
     * @return
     */
    public boolean isTableExits(String tablename) {
        // boolean result = false;// 表示不存在
        Cursor cursor = null;
        try {
            checkLock();
            String str = "select count(*) xcount  from  " + tablename;
            cursor = db.rawQuery(str, null);
            if (null != cursor && cursor.moveToFirst()) {
                return true;
            }
        } catch (Exception ex) {
            return false;
        } finally {
            if (null != cursor) {
                cursor.close();
            }
        }
        return false;
    }

    public boolean isTableExits(DybDB edb, Object dbobj, String tablename) {
        // boolean result=true;//表示不存在
        if (null == edb)
            return false;
        Cursor cursor = null;
        try {
            checkLock();
            String str = "select count(*) xcount  from  " + tablename;
            cursor = ((SQLiteDatabase) dbobj).rawQuery(str, null);
            if (null != cursor && cursor.moveToFirst()) {
                return true;
            }
        } catch (Exception ex) {
            return false;
        } finally {
            if (null != cursor) {
                cursor.close();
            }
        }
        return false;
    }

    /**
     * @param tableName
     * @return
     */
    public boolean tabbleIsExist(String tableName) {
        boolean result = false;
        if (tableName == null) {
            return false;
        }
        Cursor cursor = null;
        try {
            String sql =
                    "select count(*) as c from Sqlite_master  where type ='table' and name ='" + tableName.trim()
                            + "' ";
            cursor = db.rawQuery(sql, null);
            if (cursor.moveToNext()) {
                int count = cursor.getInt(0);
                if (count > 0) {
                    result = true;
                }
            }

        } catch (Exception e) {
            result = false;
        } finally {
            if (null != cursor && !cursor.isClosed()) {
                cursor.close();
            }
        }
        return result;
    }

    /***
     * 关闭 获取SQLite数据库连接
     *
     * @return SQLiteDatabase
     */
    public SQLiteDatabase getConnection() {
        checkLock();
        return db;
    }

    /**
     * @param tablename
     */
    private void saveTableName(String tablename) {
        if (!isTableExits("EtionTable")) {
            db = getConnection();
            StringBuffer sql = new StringBuffer();
            sql.append("CREATE TABLE IF NOT EXISTS   EtionTable (");
            sql.append(" id_DB  integer   primary key  AUTOINCREMENT , ");
            sql.append(" tablename  VARCHAR  ) ");
            checkLock();
            db.execSQL(sql.toString());
        }
        ContentValues values = new ContentValues();
        values.put("tablename", tablename);
        save("EtionTable", values);
    }

    /**
     * 获取数据库所有的表名
     *
     * @return
     */
    public String[] getTableNameDB(SQLiteDatabase db) {
        try {
            Vector<String> values = new Vector<String>();
            Cursor cursor = find("select *  from   EtionTable ", null);
            while (cursor.moveToNext()) {
                values.add(cursor.getString(cursor.getColumnIndex("tablename")));
            }
            cursor.close();
            return values.toArray(new String[]{});
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public void close() {
        try {
            if (db != null && db.isOpen()) {
                db.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    /***
     * 关闭 SQLite数据库连接
     *
     * @return
     */
    public void closeConnection() {
        try {
            if (db != null && db.isOpen()) {
                db.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public boolean importDatabase(String dbPath) throws IOException {
        // Close the SQLiteOpenHelper so it will commit the created empty
        // database to internal storage.
        close();
        File newDb = new File(dbPath);
        File oldDb = new File(DB_FILEPATH);
        if (newDb.exists()) {
            copyFile(new FileInputStream(newDb), new FileOutputStream(oldDb));
            // Access the copied database so SQLiteHelper will cache it and mark
            // it as created.
            getWritableDatabase().close();
            return true;
        }
        return false;
    }

    public boolean exportDatabase(String dbPath) throws IOException {
        // Close the SQLiteOpenHelper so it will commit the created empty
        // database to internal storage.
        close();
        File backupDb = new File(dbPath);
        File originDb = new File(DB_FILEPATH);
        if (originDb.exists()) {
            copyFile(new FileInputStream(originDb), new FileOutputStream(backupDb));
            // Access the copied database so SQLiteHelper will cache it and mark
            // it as created.
            getWritableDatabase().close();
            return true;
        }
        return false;
    }

    public void copyFile(FileInputStream fromFile, FileOutputStream toFile) throws IOException {
        FileChannel fromChannel = null;
        FileChannel toChannel = null;
        try {
            fromChannel = fromFile.getChannel();
            toChannel = toFile.getChannel();
            fromChannel.transferTo(0, fromChannel.size(), toChannel);
        } finally {
            try {
                if (fromChannel != null) {
                    fromChannel.close();
                }
            } finally {
                if (toChannel != null) {
                    toChannel.close();
                }
            }
        }
    }

}
